<?php

namespace app\models;

/**
 * 充值
 * Class Recharge
 * @package app\models
 */
class Recharge
{
    public static function getCount($institutionCode, $status, $startTime, $endTime)
    {
        $sql = 'SELECT COUNT(1) FROM v_recharge WHERE 1=1';
        if ($institutionCode != '0') {
            $sql = $sql . ' AND institution_code=:institutionCode';
        }
        if ($status != '0') {
            $sql = $sql . ' AND status=:status';
        }
        if ($startTime != '') {
            $sql = $sql . ' AND create_datetime>=:startTime';
        }
        if ($endTime != '') {
            $sql = $sql . ' AND create_datetime<:endTime';
        }
        $cmd = \Yii::$app->db->createCommand($sql);
        if ($institutionCode != '0') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }
        if ($startTime != '') {
            $cmd->bindValue(':startTime', $startTime);
        }
        if ($endTime != '') {
            $cmd->bindValue(':endTime', $endTime);
        }
        $count = $cmd->queryScalar();
        return $count;
    }


    public static function getCountWithChildren($institutionCode, $status, $startTime, $endTime)
    {
        $sql = 'SELECT COUNT(1) FROM v_recharge LEFT JOIN t_institution ON t_institution.code = v_recharge.institution_code 
                WHERE 1=1 AND t_institution.parent_code=:institutionCode';

        if ($status != '0') {
            $sql = $sql . ' AND status=:status';
        }
        if ($startTime != '') {
            $sql = $sql . ' AND create_datetime>=:startTime';
        }
        if ($endTime != '') {
            $sql = $sql . ' AND create_datetime<:endTime';
        }
        $cmd = \Yii::$app->db->createCommand($sql);
        if ($institutionCode != '0') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }
        if ($startTime != '') {
            $cmd->bindValue(':startTime', $startTime);
        }
        if ($endTime != '') {
            $cmd->bindValue(':endTime', $endTime);
        }

        $count = $cmd->queryScalar();
        return $count;
    }

    /**
     * * 获取充值列表
     * @param int $offset 分页查询起始位置
     * @param int $limit 分页查询每页数量
     * @param string $institutionCode
     * @param int $status
     * @return array
     */
    public static function getList($offset, $limit, $institutionCode, $status, $startTime, $endTime)
    {
        $filter = '';
        if ($institutionCode != '0') {
            $filter = $filter . ' AND institution_code=:institutionCode';
        }
        if ($status != '0') {
            $filter = $filter . ' AND status=:status';
        }
        if ($startTime != '') {
            $filter = $filter . ' AND create_datetime>=:startTime';
        }
        if ($endTime != '') {
            $filter = $filter . ' AND create_datetime<:endTime';
        }
        $sql = "SELECT * FROM v_recharge WHERE 1=1 $filter ORDER BY create_datetime DESC LIMIT :offset, :limit";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);
        if ($institutionCode != '0') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }

        if ($startTime != '') {
            $cmd->bindValue(':startTime', $startTime);
        }
        if ($endTime != '') {
            $cmd->bindValue(':endTime', $endTime);
        }
        $items = $cmd->queryAll();

        return $items;
    }

    public static function getListWithChildren($offset, $limit, $institutionCode, $status, $startTime, $endTime)
    {
        $filter = '';
        if ($status != '0') {
            $filter = $filter . ' AND status=:status';
        }
        if ($startTime != '') {
            $filter = $filter . ' AND create_datetime>=:startTime';
        }
        if ($endTime != '') {
            $filter = $filter . ' AND create_datetime<:endTime';
        }
        $sql = "SELECT * FROM v_recharge LEFT JOIN t_institution ON t_institution.code = v_recharge.institution_code
                  WHERE 1=1 AND t_institution.parent_code=:institutionCode     
                  $filter ORDER BY v_recharge.create_datetime DESC LIMIT :offset, :limit";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);
        $cmd->bindValue(':institutionCode', $institutionCode);

        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }

        if ($startTime != '') {
            $cmd->bindValue(':startTime', $startTime);
        }
        if ($endTime != '') {
            $cmd->bindValue(':endTime', $endTime);
        }

        $items = $cmd->queryAll();

        return $items;
    }

    /**
     * 获取充值详情
     * @param int $rechargeId
     * @return array|false
     */
    public static function getDetail($rechargeId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_recharge WHERE id=:rechargeId');
        $cmd->bindValue(':rechargeId', $rechargeId);
        $item = $cmd->queryOne();

        return $item;
    }

    /**
     * 获取充值附件
     * @param $rechargeId
     * @return array
     */
    public static function getAttachment($rechargeId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_recharge_attachment WHERE recharge_id=:rechargeId');
        $cmd->bindValue(':rechargeId', $rechargeId);
        $item = $cmd->queryAll();

        return $item;
    }

    /**
     * 新增充值
     * @param mixed $data
     * @return int
     * @throws \yii\db\Exception
     */
    public static function add($institution_code, $amount, $mode, $payment_name, $receivable_name, $receivable_bank, $receivable_account, $remark, $file_list,$currentInstitutionCode)
    {
        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();
        $result = 0;

        try {
            //添加recharge
            $cmd = \Yii::$app->db->createCommand('call p_add_recharge(:in_institution_code, :in_amount, :in_mode, :in_payment_name, :in_receivable_name, :in_receivable_bank, :in_receivable_account, :in_remark)');
            $cmd->bindValue(':in_institution_code', $institution_code);
            $cmd->bindValue(':in_amount', $amount);
            $cmd->bindValue(':in_mode', $mode);
            $cmd->bindValue(':in_payment_name', $payment_name);
            $cmd->bindValue(':in_receivable_name', $receivable_name);
            $cmd->bindValue(':in_receivable_bank', $receivable_bank);
            $cmd->bindValue(':in_receivable_account', $receivable_account);
            $cmd->bindValue(':in_remark', $remark);
            $recharge_id = $cmd->queryScalar();

            if (isset($file_list) && count($file_list)) {
                for ($i = 0; $i < count($file_list); $i++) {
                    $sql = 'INSERT INTO t_recharge_attachment(id,recharge_id,name,url) VALUES (uuid(),:recharge_id,:name,:url)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':recharge_id', $recharge_id);
                    $cmd->bindValue(':name', $file_list[$i]['name']);
                    $cmd->bindValue(':url', $file_list[$i]['url']);

                    $cmd->execute();
                }
            }
            // 处理机构及其所有上级机构的帐

            $cmd = \Yii::$app->db->createCommand('select code_tree from t_institution where code=:institutionCode');
            $cmd->bindValue(':institutionCode', $institution_code);//选中的机构
            $codeTree = $cmd->queryScalar();
            $institutionCodes = str_split($codeTree, 8);
            //反转，从子机构向父机构方向循环
            $institutionCodes=array_reverse($institutionCodes);
            foreach ($institutionCodes as $institutionCode) {
                if($currentInstitutionCode==$institutionCode){
                    break;//如果等于了，说明当前管理员机构已经到了
                }
                if($institution_code==$institutionCode){
                    continue;//上面已经加过了
                }
                $cmd = \Yii::$app->db->createCommand('call p_add_recharge(:in_institution_code, :in_amount, :in_mode, :in_payment_name, :in_receivable_name, :in_receivable_bank, :in_receivable_account, :in_remark)');
                $cmd->bindValue(':in_institution_code', $institutionCode);
                $cmd->bindValue(':in_amount', $amount);
                $cmd->bindValue(':in_mode', $mode);
                $cmd->bindValue(':in_payment_name', $payment_name);
                $cmd->bindValue(':in_receivable_name', $receivable_name);
                $cmd->bindValue(':in_receivable_bank', $receivable_bank);
                $cmd->bindValue(':in_receivable_account', $receivable_account);
                $origin_institution_name=Institution::getInstitutionInfo($institution_code)['name'];
                $cmd->bindValue(':in_remark', $remark."(因： $origin_institution_name 上账)");
                $recharge_id = $cmd->queryScalar();
            }

            ///

            $transaction->commit();
            $result = 1;
        } catch (\Exception $e) {
            $result = 0;
            $transaction->rollBack();
        }

        return $result;
    }

    /**
     * 充值审核
     * @param mixed $data
     * @return int
     * @throws \yii\db\Exception
     */
    public static function approve($data)
    {
        $status = 1;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('call p_approve_recharge(:in_recharge_id, :in_recharge_status)');
            $cmd->bindValue(':in_recharge_id', $data['recharge_id']);
            $cmd->bindValue(':in_recharge_status', $data['recharge_status']);
            $status = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    /*
     * 审核通过的充值记录
     */
    public static function getRecharged($institutionId)
    {
        $sql = "SELECT count(*) AS totalnum ,sum(amount) AS totalamount FROM t_recharge where `status`=11 AND
                 institution_code=:institutionId";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionId', $institutionId);
        $result = $cmd->queryOne();

        if ($result['totalnum'] == '0') {
            $result['totalamount'] = 0;
        }
        return $result['totalamount'];
    }

    /**
     *获取该机构最近的充值记录
     * @param $institutionCode
     * @return array|false|null
     */
    public static function getLastRecharged($institutionCode)
    {
        $sql = '';
        if ($institutionCode == '0') {
            $sql = 'SELECT * FROM t_recharge
                WHERE status = 11
                ORDER BY create_datetime DESC';
        } else {
            $sql = 'SELECT * FROM t_recharge
                WHERE institution_code = :institution_code AND status = 11
                ORDER BY create_datetime DESC';
        }
        $cmd = \Yii::$app->db->createCommand($sql);
        if ($institutionCode != '0') {
            $cmd->bindValue(':institution_code', $institutionCode);
        }
        $result = $cmd->queryOne();
        if ($result == false) {
            $result = null;
        }
        return $result;
    }

    /**
     * 获取当前年内所有月该机构的充值统计
     * @param $institution_code
     * @return array
     */
    public static function getRechargedAmountByMonth($institution_code, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = $filter . '  institution_code = :institution_code ';
        } else {
            $filter = $filter . ' 1 = 1 ';
        }
        $sql = "SELECT t1.m as date,ifnull(t2.amount,0) AS amount FROM (
                SELECT concat(year(now()),'-',t.m) AS m FROM (
                  SELECT '01' AS m UNION ALL
                  SELECT '02' UNION ALL
                  SELECT '03' UNION ALL
                  SELECT '04' UNION ALL
                  SELECT '05' UNION ALL
                  SELECT '06' UNION ALL
                  SELECT '07' UNION ALL
                  SELECT '08' UNION ALL
                  SELECT '09' UNION ALL
                  SELECT '10' UNION ALL
                  SELECT '11' UNION ALL
                  SELECT '12' ) as t) t1
                  LEFT JOIN (
                    SELECT DATE_FORMAT(create_datetime, '%Y-%m') as m,sum(amount) AS amount FROM t_recharge
                    WHERE status = 11
                          AND $filter
                          AND year(create_datetime) = year(now())
                    GROUP BY DATE_FORMAT(create_datetime, '%Y-%m')
                    ) t2 ON t2.m = t1.m
                ORDER BY t1.m";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institution_code);
        }

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取当前年内所有季度该机构的充值统计
     * @param $institution_code
     * @return array
     */
    public static function getRechargedAmountByQuarter($institution_code, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = $filter . '  institution_code = :institution_code ';
        } else {
            $filter = $filter . ' 1 = 1 ';
        }

        $sql = "SELECT t1.q as date,ifnull(t2.amount,0) AS amount FROM (
                  SELECT concat(year(now()),'年', '第一季度') AS q,1 AS qv UNION ALL
                  SELECT concat(year(now()),'年', '第二季度'),2 UNION ALL
                  SELECT concat(year(now()),'年', '第三季度'),3 UNION ALL
                  SELECT concat(year(now()),'年', '第四季度'),4
                    ) AS t1
                  LEFT JOIN (
                  SELECT QUARTER(create_datetime) as qv,sum(amount) AS amount FROM t_recharge
                  WHERE status = 11
                        AND $filter
                        AND year(create_datetime) = year(now())
                  GROUP BY QUARTER(create_datetime)
                  ) t2 ON t2.qv = t1.qv
                ORDER BY t1.qv";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institution_code);
        }
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取该机构按年的充值统计
     * @param $institution_code
     * @return array
     */
    public static function getRechargedAmountByYear($institution_code, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = $filter . '  institution_code = :institution_code ';
        } else {
            $filter = $filter . ' 1 = 1 ';
        }

        $sql = "SELECT year(create_datetime) as date,ifnull(sum(amount),0) AS amount FROM t_recharge 
                WHERE status = 11
                        AND $filter
                GROUP BY year(create_datetime)
                ORDER BY year(create_datetime)";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institution_code);
        }
        $result = $cmd->queryAll();
        return $result;
    }


}